﻿---- theo hãng hàng không VNA
--LẤY DOANH SỐ BẢO LÃNH
--select distinct CUST_ID 
--FROM [server16].[VPB_WHR2].[dbo].[MD_RPT_DAILY_VPB] a 
--   where  a.FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'
--   and a.VALUE_DATE between '20150601' and '20151117'

   	DECLARE @DATE_DT NVARCHAR (20)
	SET @DATE_DT='20160501'

	DECLARE @DATE_CT NVARCHAR (20)
	SET @DATE_CT='20160531'

		DECLARE @DATE1 NVARCHAR (20) ----- tháng báo cáo
	SET @DATE1='201605'

	 	DECLARE @DATEDT NVARCHAR (20)
	SET @DATEDT='20160501'

	DECLARE @DATECT NVARCHAR (20)
	SET @DATECT='20160531'

----LẤY DƯ BẢO LÃNH THEO CHUỐI THÁNG, VÙNG, TRUNG TÂM
PRINT('1')

	EXEC('SELECT  A.BUSINESS_DATE, A.COMPANY, CCY, A.ID,A.AMOUNT_LCY/(-1E9) AS AMOUNT, A.CUST_ID,
	A.SECTOR, A.VALUE_DATE, A.MATURITDATE, C.CUS_NAME,B.NAME AS BRANCH_NAME, ''VUNG ''+B.REGION AS REGION 
, A.SEGMENT, FUND_PROGRAM =''TAI TRO DAI LY BAN VE CUA HHK''
				INTO DU_BL_VNA_'+@DATE_CT+'
				       FROM SERVER16.[VPB_WHR2].[DBO].[CRB_ALL] A
						LEFT JOIN [ANHCP].[CEO_PERFORMANCE].DBO.SME_BRANCHCODE B
						ON A.COMPANY COLLATE DATABASE_DEFAULT = B.BRANCH
						LEFT JOIN SERVER16.VPB_WHR2.DBO.VPB_CUSTOMER C
						ON A.CUST_ID=C.RECID
				WHERE A.GL IN (''9210'',''9220'',''9260'',''9270'',''9280'') ------------Đầu bảo lãnh
						AND A.BUSINESS_DATE = '''+@DATE_CT+'''
							------AND ((A.SEGMENT<>''KHCN'' AND A.COMPANY NOT IN (SELECT BRANCH_CODE FROM [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF))
							------			OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))

and ((A.SEGMENT not in (''KHCN'',''HH'') 
and A.COMPANY not in (select branch_code from [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF
 where branch_code <> ''VN0010289''))
              or (A.SEGMENT is null and LEFT(A.SECTOR,1)>1))


						AND A.ID IN ( SELECT MD_ID FROM SERVER16.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
									WHERE BUSINESS_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+'''
									AND FUND_PROGRAM = ''Tai tro dai ly ban ve cua HHK'') ')



PRINT('2')
EXEC ('
INSERT DU_BL_VNA
SELECT * ----INTO DU_BL_VNA 
FROM DU_BL_VNA_'+@DATE_CT+'')

PRINT('3')
EXEC('INSERT SERVER12.ANHCP.DBO.DU_BL_VNA
SELECT * FROM DU_BL_VNA_'+@DATE_CT+'')


------delete from DU_BL_VNA where BUSINESS_DATE between '20160201' and '20160229' AND FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'
------delete from SERVER12.ANHCP.DBO.DU_BL_VNA where BUSINESS_DATE between '20160201' and '20160229' AND FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'


------DELETE from DU_BL_VNA 
------DELETE  from SERVER12.ANHCP.DBO.DU_BL_VNA

----LẤY DOANH SỐ BẢO LÃNH THEO CHUỐI THÁNG, VÙNG, TRUNG TÂM
PRINT('4')
EXEC ('SELECT 
 BUSINESS_DATE,MD_ID,CUST_ID,C.CUS_NAME, VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
 BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM 
 INTO DS_BL_VNA
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE_CT+'''  ----------------------------------------CHOT NGAY CUOI THANG 
  AND VALUE_DATE BETWEEN '''+@DATE_DT+''' AND '''+@DATE_CT+''' 
	AND FUND_PROGRAM = ''Tai tro dai ly ban ve cua HHK''
	AND MD_ID IN (SELECT ID FROM DU_BL_VNA_'+@DATE_CT+')')


PRINT('5')
	EXEC('SELECT * ,ROW_NUMBER() OVER( PARTITION BY MD_ID ORDER BY BUSINESS_DATE) AS RN
INTO DS_BL_VNA_1
FROM DS_BL_VNA
ORDER BY BUSINESS_DATE')


PRINT('6')
	EXEC('SELECT [BUSINESS_DATE]
      ,[MD_ID]
      ,[CUST_ID]
	  ,CUS_NAME
      ,[VALUE_DATE]
      ,[MUC_DICH]
      ,[CUS_SEGMENT]
      ,[AMOUNT_QD]/1E9  AS AMOUNT_QD
      ,[BRANCH_NAME_SME]
      ,[ZONE_ID_SME]
      ,[CO_CODE]
      ,[CATEGORY]
      ,[FUND_PROGRAM]
      ,[RN] 
	   INTO DS_BL_VNA_FINAL
FROM DS_BL_VNA_1
WHERE RN=1 ')

--UPDATE MD_FINAL
--SET BUSINESS_DATE='20151130'------------------------------UPDATE NGAY BÁO CÁO CUỐI THÁNG NẾU INSERT VÀO CÙNG 1 BẢNG TỔNG HỢP

PRINT('7')
EXEC('SELECT *,ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY BUSINESS_DATE) AS COUNT_CIF 
INTO DS_BL_VNA_FINAL_1
 FROM DS_BL_VNA_FINAL')

 PRINT('8')
EXEC('UPDATE DS_BL_VNA_FINAL_1
 SET COUNT_CIF=0
 WHERE COUNT_CIF>1')

PRINT('9')
EXEC('UPDATE DS_BL_VNA_FINAL_1
SET ZONE_ID_SME=''CHUA PHAN BO''
WHERE ZONE_ID_SME IS NULL')
PRINT('10')

EXEC('INSERT DOANH_SO_BAO_LANH_HTC
SELECT * FROM DS_BL_VNA_FINAL_1')
PRINT('11')

EXEC('INSERT SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC
SELECT * , VALUE_DATE AS TO_VALUE_DATE FROM DS_BL_VNA_FINAL_1 ')------ căn cứ vào value_date để xem phát sinh doanh số


--delete from DOANH_SO_BAO_LANH_HTC where VALUE_DATE BETWEEN '20160201' AND '20160229' 
--	AND FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'

--delete FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC where   from_VALUE_DATE BETWEEN '20160201' AND '20160229' 
--	AND FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'

--SELECT * FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC
--DELETE DOANH_SO_BAO_LANH_HTC
--DELETE FROM SERVER12.ANHCP.DBO.DOANH_SO_BAO_LANH_HTC
--WHERE FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'

----- THẤU CHI

--SELECT BUSINESS_DATE, CIF,CUSTOMER_NAME, T24_ACC_NO, CO_CODE, CUS_COMPANY_BOOK,  
--ONLINE_ACTUAL_BAL*B.MID_RATE
--,overdraft_type, SEGMENT
--FROM [SERVER16].[VPB_WHR2].[dbo].[VAYTHE] A
--LEFT JOIN
--ON A.CCY=
--where overdraft_type ='Tai tro dai ly ban ve cua HHK'

----LẤy TRÊN BALANCE ENDMONTH


--SELECT TOP 3 *   FROM [SERVER74].[BICDATA].[DBO].[BALANCE_ENDMONTH] A


	--DECLARE @DATE1 NVARCHAR (20)
	--SET @DATE1='201512'

	-- 	DECLARE @DATEDT NVARCHAR (20)
	--SET @DATEDT='20151201'

	--DECLARE @DATECT NVARCHAR (20)
	--SET @DATECT='20151231'

PRINT('12')
EXEC('SELECT YEARMONTH, ID, CIF, CUSTOMER_NAME, OPENNING_DATE, MUC_DICH,BI_SEGMENT, BAL_QD/1E9 AS BAL_QD,
B.BRANCH_NAME_SME, B.ZONE_ID_SME, A.BRANCH_CODE, CATEGORY, OVERDRAFT_TYPE =''TAI TRO DAI LY BAN VE CUA HHK''
INTO THAUCHI_1 
 FROM [SERVER74].[BICDATA].[DBO].[BALANCE_ENDMONTH] A
 LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] B
 ON A.BRANCH_CODE=B.BRANCH_ID
 WHERE PRODUCT_NAME=''6. OVERDRAFT''
 and ACCTNO IN (SELECT T24_ACC_NO
FROM [SERVER16].[VPB_WHR2].[dbo].[VAYTHE] A
WHERE OVERDRAFT_TYPE =''TAI TRO DAI LY BAN VE CUA HHK'' AND BUSINESS_DATE BETWEEN '''+@DATEDT+''' AND '''+@DATECT+''')
 AND YEARMONTH='''+@DATE1+''' ')
 PRINT('13')
 	EXEC('SELECT * ,ROW_NUMBER() OVER( PARTITION BY ID ORDER BY YEARMONTH) AS RN
INTO THAUCHI_2
FROM THAUCHI_1
ORDER BY YEARMONTH')

PRINT('14')
	EXEC('SELECT YEARMONTH, ID, CIF, CUSTOMER_NAME, OPENNING_DATE, MUC_DICH,BI_SEGMENT, BAL_QD,
BRANCH_NAME_SME, ZONE_ID_SME, BRANCH_CODE, CATEGORY, OVERDRAFT_TYPE
      ,[RN] 
	   INTO THAUCHI_3
FROM THAUCHI_2
WHERE RN=1 ')


PRINT('15')
EXEC('SELECT *,ROW_NUMBER() OVER (PARTITION BY CIF ORDER BY YEARMONTH) AS COUNT_CIF 
INTO THAUCHI_4
 FROM THAUCHI_3')
 PRINT('16')
EXEC('UPDATE THAUCHI_4
 SET COUNT_CIF=0
 WHERE COUNT_CIF>1')

 PRINT('17')
EXEC('UPDATE THAUCHI_4
SET ZONE_ID_SME=''CHUA PHAN BO''
WHERE ZONE_ID_SME IS NULL')
PRINT('18')
EXEC('
INSERT THAU_CHI_VNA
SELECT * ----INTO THAU_CHI_VNA 
FROM THAUCHI_4')

EXEC('
INSERT SERVER12.ANHCP.DBO.THAU_CHI_VNA
SELECT * FROM THAUCHI_4 ')------ căn cứ vào value_date để xem phát sinh doanh số

--delete from THAU_CHI_VNA where YEARMONTH='201602'
--delete from SERVER12.ANHCP.DBO.THAU_CHI_VNA where YEARMONTH='201602'

--DELETE select distinct overdraft_type from THAU_CHI_VNA

 EXEC('DROP TABLE DU_BL_VNA_'+@DATE_CT+'
  DROP TABLE DS_BL_VNA
  DROP TABLE DS_BL_VNA_1
 DROP TABLE  DS_BL_VNA_FINAL
  DROP TABLE DS_BL_VNA_FINAL_1
  DROP TABLE THAUCHI_1
  DROP TABLE THAUCHI_2
 DROP TABLE THAUCHI_3
 DROP TABLE THAUCHI_4')

 --select max(yearmonth) from SERVER12.ANHCP.DBO.THAU_CHI_VNA

 ---------------------------------------------------------------------------------------

  ----=======********HẠN MỨC KHÁCH HÀNG THAM GIA TÀI TRỢ CHUỖI VNA


	--DECLARE @DATE_CT NVARCHAR (20)
	--SET @DATE_CT='20151231'
  ----1. BẢO LÃNH
  Print('19')
  EXEC('SELECT *,
(B.MID_RATE* 
(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
      INTO LIMIT_ORG_1
  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
  LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
AND B.SAVE_DATE ='''+@DATE_CT+'''
  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
  AND LIABILITY_NUMBER IN (SELECT DISTINCT CUST_ID FROM SERVER12.ANHCP.DBO.DU_BL_VNA
  WHERE FUND_PROGRAM=''Tai tro dai ly ban ve cua HHK'')
  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%''
  ) ')


    Print('20')
EXEC('SELECT DISTINCT CUST_ID, DATE='''+@DATE_CT+''', 
c.CUS_NAME, D.ZONE_ID_SME, D.BRANCH_NAME_SME, FUND_PROGRAM, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING INTO LIMIT_ORG_2
FROM SERVER12.ANHCP.DBO.DU_BL_VNA A
LEFT JOIN (SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
ON A.CUST_ID=B.LIABILITY_NUMBER
left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] c
on a.CUST_ID=c.RECID
LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] D
ON C.COMPANY_BOOK =D.BRANCH_ID
WHERE FUND_PROGRAM=''TAI TRO DAI LY BAN VE CUA HHK'' ')

  Print('21')
EXEC('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
select A.* , HINH_THUC_TAI_TRO=''BAO LANH'' 
from LIMIT_ORG_2 A')

--select * from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'

--delete from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'
  Print('22')
EXEC('DROP TABLE LIMIT_ORG_1')


  ----2, THẤU CHI
  Print('23')
  EXEC('SELECT *,
(B.MID_RATE* 
(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
WHEN INTERNAL_AMOUNT LIKE ''%M%'' THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
      INTO LIMIT_ORG_1
  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
  LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
ON A.LIMIT_CURRENCY=B.CODE
AND B.SAVE_DATE ='''+@DATE_CT+'''
  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
  AND LIABILITY_NUMBER IN (SELECT DISTINCT CIF FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA)
  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%''
  ) ')

  --SELECT *  FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA A where cif='1557248'
  Print('24')
EXEC ('SELECT DISTINCT CIF, DATE='''+@DATE_CT+''', 
c.CUS_NAME, D.ZONE_ID_SME, D.BRANCH_NAME_SME,
 OVERDRAFT_TYPE, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING into LIMIT_ORG_3
FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA A
LEFT JOIN 
(SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
ON A.CIF=B.LIABILITY_NUMBER
left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] c
on a.CIF=c.RECID
LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] D
ON C.COMPANY_BOOK =D.BRANCH_ID ')

  Print('25')
EXEC ('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
select A.* , HINH_THUC_TAI_TRO=''THAU CHI'' 
from LIMIT_ORG_3 A')

--select * from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'
--delete from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'
  Print('26')
EXEC('INSERT SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG ----------------BẢNG TỔNG HỢP CÁC CHUỖI
select *,  date as to_date from LIMIT_ORG_2
union 
select *, date as to_date from LIMIT_ORG_3')

--delete from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG where from_date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'
  Print('27')
EXEC('DROP TABLE LIMIT_ORG_1
DROP TABLE LIMIT_ORG_2
DROP TABLE LIMIT_ORG_3')

--DELETE select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG 
--DELETE LIMIT_ORG_TTTM_CHUOI_VNA
  Print('28')
exec('update a
set a.branch_name=b.branch_name_sme, a.region=b.zone_id_sme
from  SERVER12.ANHCP.DBO.DU_BL_VNA a, server74.bicdata.dbo.branch_code b
where a.company=b.branch_id')
  Print('29')
exec('update a
set a.branch_name_sme=b.branch_name_sme, a.zone_id_sme=b.zone_id_sme
from  SERVER12.ANHCP.DBO.THAU_CHI_VNA a, server74.bicdata.dbo.branch_code b
where a.branch_code=b.branch_id
--and a.branch_name_sme is null
--and a.zone_id_sme is null')


--'check KH moi VNA'
--SELECT * into #a FROM SERVER16.VPB_WHR2.DBO.[MD_RPT_DAILY_VPB]
--									WHERE BUSINESS_DATE >='20160101'
--									AND FUND_PROGRAM = 'Tai tro dai ly ban ve cua HHK'


--SELECT * into #b
--FROM [SERVER16].[VPB_WHR2].[dbo].[VAYTHE] A
--WHERE OVERDRAFT_TYPE ='TAI TRO DAI LY BAN VE CUA HHK' AND BUSINESS_DATE >='20160101'


--select * from #a where value_date >='20160101'
--select distinct cif from #b where openning_date>='20160101'















--SELECT DISTINCT FUND_PROGRAM FROM  SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG

--DELETE FROM  SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG
--where from_date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'


--select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG
--where from_date between '20160401' and '20160429' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'



-- ----=======********HẠN MỨC KHÁCH HÀNG THAM GIA TÀI TRỢ CHUỖI VNA


--	--DECLARE @DATE_CT NVARCHAR (20)
--	--SET @DATE_CT='20151231'
--  ----1. BẢO LÃNH
--  Print('19')
--  EXEC('SELECT *,
--(B.MID_RATE* 
--(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
--WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
--ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
--REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
--WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
--      INTO LIMIT_ORG_1
--  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
--  LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
--ON A.LIMIT_CURRENCY=B.CODE
--AND B.SAVE_DATE ='''+@DATE_CT+'''
--  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
--  AND LIABILITY_NUMBER IN (SELECT DISTINCT CUST_ID FROM SERVER12.ANHCP.DBO.DU_BL_VNA
--  WHERE FUND_PROGRAM=''Tai tro dai ly ban ve cua HHK'')
--  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%''
--  ) ')



--EXEC('SELECT DISTINCT CUST_ID, DATE='''+@DATE_CT+''', 
--c.CUS_NAME, D.ZONE_ID_SME, D.BRANCH_NAME_SME, FUND_PROGRAM, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING INTO LIMIT_ORG_2
--FROM SERVER12.ANHCP.DBO.DU_BL_VNA A
--LEFT JOIN (SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
--ON A.CUST_ID=B.LIABILITY_NUMBER
--left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] c
--on a.CUST_ID=c.RECID
--LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] D
--ON C.COMPANY_BOOK =D.BRANCH_ID
--WHERE FUND_PROGRAM=''TAI TRO DAI LY BAN VE CUA HHK'' ')


--EXEC('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
--select A.* , HINH_THUC_TAI_TRO=''BAO LANH'' 
--from LIMIT_ORG_2 A')

----delete from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160201' and '20160229' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'

--EXEC('DROP TABLE LIMIT_ORG_1')


--  ----2, THẤU CHI

--  EXEC('SELECT *,
--(B.MID_RATE* 
--(CASE WHEN [INTERNAL_AMOUNT] LIKE''%B%''THEN REPLACE([INTERNAL_AMOUNT],''B'',''000000000'')
--WHEN INTERNAL_AMOUNT LIKE ''%M%'' THEN REPLACE([INTERNAL_AMOUNT],''M'',''000000'') 
--ELSE [INTERNAL_AMOUNT] END))/1E9 AS [INTERNAL_AMOUNT_QD],
--REVOLVING = CASE WHEN ID LIKE''%.0050000%'' THEN ''Y''
--WHEN ID LIKE ''%.0060000%'' THEN ''N'' END
--      INTO LIMIT_ORG_1
--  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] A
--  LEFT JOIN [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE B
--ON A.LIMIT_CURRENCY=B.CODE
--AND B.SAVE_DATE ='''+@DATE_CT+'''
--  WHERE BUSINESS_DATE='''+@DATE_CT+'''  --THAY NGAY--
--  AND LIABILITY_NUMBER IN (SELECT DISTINCT CIF FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA)
--  AND (ID LIKE''%.0050000%'' OR ID LIKE ''%.0060000%''
--  ) ')

--  --SELECT *  FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA A where cif='1557248'

--EXEC ('SELECT DISTINCT CIF, DATE='''+@DATE_CT+''', 
--c.CUS_NAME, D.ZONE_ID_SME, D.BRANCH_NAME_SME,
-- OVERDRAFT_TYPE, [INTERNAL_AMOUNT_QD] AS LIMIT_AMOUNT, REVOLVING into LIMIT_ORG_3
--FROM SERVER12.ANHCP.DBO.THAU_CHI_VNA A
--LEFT JOIN 
--(SELECT LIABILITY_NUMBER, REVOLVING, SUM([INTERNAL_AMOUNT_QD]) AS [INTERNAL_AMOUNT_QD] FROM LIMIT_ORG_1 GROUP BY LIABILITY_NUMBER, REVOLVING) B
--ON A.CIF=B.LIABILITY_NUMBER
--left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] c
--on a.CUST_ID=c.RECID
--LEFT JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] D
--ON C.COMPANY_BOOK =D.BRANCH_ID ')


--EXEC ('INSERT LIMIT_ORG_TTTM_CHUOI_VNA
--select A.* , HINH_THUC_TAI_TRO=''THAU CHI'' 
--from LIMIT_ORG_3 A')

----delete from LIMIT_ORG_TTTM_CHUOI_VNA where date between '20160201' and '20160229' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'

--EXEC('INSERT SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG ----------------BẢNG TỔNG HỢP CÁC CHUỖI
--select *,  date as to_date from LIMIT_ORG_2
--union 
--select *, date as to_date from LIMIT_ORG_3')

----delete from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG where from_date between '20160201' and '20160229' and FUND_PROGRAM='TAI TRO DAI LY BAN VE CUA HHK'

--EXEC('DROP TABLE LIMIT_ORG_1
--DROP TABLE LIMIT_ORG_2
--DROP TABLE LIMIT_ORG_3')

----DELETE select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG 
----DELETE LIMIT_ORG_TTTM_CHUOI_VNA

--exec('update a
--set a.branch_name=b.branch_name_sme, a.region=b.zone_id_sme
--from  SERVER12.ANHCP.DBO.DU_BL_VNA a, server74.bicdata.dbo.branch_code b
--where a.company=b.branch_id')

--exec('update a
--set a.branch_name_sme=b.branch_name_sme, a.zone_id_sme=b.zone_id_sme
--from  SERVER12.ANHCP.DBO.THAU_CHI_VNA a, server74.bicdata.dbo.branch_code b
--where a.branch_code=b.branch_id
----and a.branch_name_sme is null
----and a.zone_id_sme is null')

----update a
----set a.branch_name_sme='BEN THANH', a.zone_id_sme='VUNG 06'
----from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG  a
----  where cust_id='1080832' and zone_id_sme='CHUA PHAN BO'


----select * from  SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG
----where fund_program ='TAI TRO DAI LY BAN VE CUA HHK' and from_date='20160229'

----select * from SERVER12.ANHCP.DBO.LIMIT_ORG_CARLBERG where fund_program like '%hhk%' and from_date between '20160301' and '20160331'









---------------check
--SELECT COMPANY, GL, CUST_ID,CUS_NAME, AMOUNT_FCY, a.SEGMENT
--  FROM [SERVER16].[VPB_WHR2].[DBO].[CRB_ALL] A
--   LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER B
-- ON A.CUST_ID=B.RECID
--WHERE CUST_ID IN ('1560929',
--'764352') AND BUSINESS_DATE='20160229'

--SELECT BUSINESS_DATE, MD_ID , CUST_ID,CUS_NAME MUC_DICH,  CUS_SEGMENT, FUND_PROGRAM
-- FROM [SERVER16].[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
-- LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER B
-- ON A.CUST_ID=B.RECID
--WHERE CUST_ID IN ('1560929',
--'764352') AND BUSINESS_DATE BETWEEN '20160201' AND '20160229'